{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "from pprint import pprint\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "clinical_df = pd.read_excel(\"20200414.xlsx\", sheet_name=0)\n",
    "library_df = pd.read_excel(\"20200414.xlsx\", sheet_name=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(2357, 2)"
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "source": [
    "clinical_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "  建库编号 DNA提取编号 管上编号  是否临床                 文库名 样本标签 index列表         建库日期  建库方法  \\\n0    无       无    2  Test  cfDNA-mix-CON-pl-2    P      62  2.01802e+07  Test   \n1    无       无    3  Test  cfDNA-mix-CON-pl-3    P      63  2.01802e+07  Test   \n2    无       无    1  Test        M-cfDNA-5-pl    P      61  2.01803e+07  Test   \n3    无       无    3  Test        M-cfDNA-7-pl    P      63  2.01803e+07  Test   \n4    无       无    1  Test        M-cfDNA-1-pl    P      64  2.01805e+07  Test   \n\n  试剂批次 起始量 PCR循环数  文库浓度 文库体积  操作人     备注        华大样本编号        任务单名称  \\\n0    无  20      无    24   35  LZL  甲基化文库    18S3191794  单链建库测序任务单-2   \n1    无  12      无  20.6   35  LZL  甲基化文库    18S3191795  单链建库测序任务单-2   \n2    无  20      无  9.64   35  LZL  甲基化文库    18S3191796  单链建库测序任务单-3   \n3    无  10      无  7.94   35  LZL  甲基化文库    18S3191797  单链建库测序任务单-3   \n4    无  20      无  10.8   35  LZL  甲基化文库  18L3214701-1  单链建库测序任务单-4   \n\n                   实验目的                  样本名  \n0      测试cfDNA甲基化单链建库数据  NIFTY-cfDNA-mix-CON  \n1      测试cfDNA甲基化单链建库数据  NIFTY-cfDNA-mix-CON  \n2  测试cfDNA甲基化低起始量单链建库数据            M-cfDNA-5  \n3  测试cfDNA甲基化低起始量单链建库数据            M-cfDNA-7  \n4           测试新接头WGBS数据            M-cfDNA-1  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>建库编号</th>\n      <th>DNA提取编号</th>\n      <th>管上编号</th>\n      <th>是否临床</th>\n      <th>文库名</th>\n      <th>样本标签</th>\n      <th>index列表</th>\n      <th>建库日期</th>\n      <th>建库方法</th>\n      <th>试剂批次</th>\n      <th>起始量</th>\n      <th>PCR循环数</th>\n      <th>文库浓度</th>\n      <th>文库体积</th>\n      <th>操作人</th>\n      <th>备注</th>\n      <th>华大样本编号</th>\n      <th>任务单名称</th>\n      <th>实验目的</th>\n      <th>样本名</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <td>0</td>\n      <td>无</td>\n      <td>无</td>\n      <td>2</td>\n      <td>Test</td>\n      <td>cfDNA-mix-CON-pl-2</td>\n      <td>P</td>\n      <td>62</td>\n      <td>2.01802e+07</td>\n      <td>Test</td>\n      <td>无</td>\n      <td>20</td>\n      <td>无</td>\n      <td>24</td>\n      <td>35</td>\n      <td>LZL</td>\n      <td>甲基化文库</td>\n      <td>18S3191794</td>\n      <td>单链建库测序任务单-2</td>\n      <td>测试cfDNA甲基化单链建库数据</td>\n      <td>NIFTY-cfDNA-mix-CON</td>\n    </tr>\n    <tr>\n      <td>1</td>\n      <td>无</td>\n      <td>无</td>\n      <td>3</td>\n      <td>Test</td>\n      <td>cfDNA-mix-CON-pl-3</td>\n      <td>P</td>\n      <td>63</td>\n      <td>2.01802e+07</td>\n      <td>Test</td>\n      <td>无</td>\n      <td>12</td>\n      <td>无</td>\n      <td>20.6</td>\n      <td>35</td>\n      <td>LZL</td>\n      <td>甲基化文库</td>\n      <td>18S3191795</td>\n      <td>单链建库测序任务单-2</td>\n      <td>测试cfDNA甲基化单链建库数据</td>\n      <td>NIFTY-cfDNA-mix-CON</td>\n    </tr>\n    <tr>\n      <td>2</td>\n      <td>无</td>\n      <td>无</td>\n      <td>1</td>\n      <td>Test</td>\n      <td>M-cfDNA-5-pl</td>\n      <td>P</td>\n      <td>61</td>\n      <td>2.01803e+07</td>\n      <td>Test</td>\n      <td>无</td>\n      <td>20</td>\n      <td>无</td>\n      <td>9.64</td>\n      <td>35</td>\n      <td>LZL</td>\n      <td>甲基化文库</td>\n      <td>18S3191796</td>\n      <td>单链建库测序任务单-3</td>\n      <td>测试cfDNA甲基化低起始量单链建库数据</td>\n      <td>M-cfDNA-5</td>\n    </tr>\n    <tr>\n      <td>3</td>\n      <td>无</td>\n      <td>无</td>\n      <td>3</td>\n      <td>Test</td>\n      <td>M-cfDNA-7-pl</td>\n      <td>P</td>\n      <td>63</td>\n      <td>2.01803e+07</td>\n      <td>Test</td>\n      <td>无</td>\n      <td>10</td>\n      <td>无</td>\n      <td>7.94</td>\n      <td>35</td>\n      <td>LZL</td>\n      <td>甲基化文库</td>\n      <td>18S3191797</td>\n      <td>单链建库测序任务单-3</td>\n      <td>测试cfDNA甲基化低起始量单链建库数据</td>\n      <td>M-cfDNA-7</td>\n    </tr>\n    <tr>\n      <td>4</td>\n      <td>无</td>\n      <td>无</td>\n      <td>1</td>\n      <td>Test</td>\n      <td>M-cfDNA-1-pl</td>\n      <td>P</td>\n      <td>64</td>\n      <td>2.01805e+07</td>\n      <td>Test</td>\n      <td>无</td>\n      <td>20</td>\n      <td>无</td>\n      <td>10.8</td>\n      <td>35</td>\n      <td>LZL</td>\n      <td>甲基化文库</td>\n      <td>18L3214701-1</td>\n      <td>单链建库测序任务单-4</td>\n      <td>测试新接头WGBS数据</td>\n      <td>M-cfDNA-1</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 81
    }
   ],
   "source": [
    "library_df.fillna('无').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "2209"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "clinical_df[\"样本编号\"].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleId_count = clinical_df[\"样本编号\"].value_counts()\n",
    "sampleId_unique = sampleId_count.index[sampleId_count==1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(2065,)"
     },
     "metadata": {},
     "execution_count": 93
    }
   ],
   "source": [
    "sampleId_unique.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "library_df = library_df.fillna('无')\n",
    "library_df[\"华大样本编号P\"] = library_df[\"华大样本编号\"].str.slice_replace(2, 3, repl='P')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(2138, 21)"
     },
     "metadata": {},
     "execution_count": 83
    }
   ],
   "source": [
    "library_df_norepeat = library_df.drop_duplicates([\"管上编号\", \"是否临床\", \"文库名\", \"样本标签\", \"index列表\", \"建库日期\", \"建库方法\", \"试剂批次\", \"起始量\", \"PCR循环数\", \"文库浓度\", \"文库体积\", \"操作人\", \"备注\", \"任务单名称\", \"实验目的\", \"样本名\"])\n",
    "library_df_norepeat.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(2197, 21)"
     },
     "metadata": {},
     "execution_count": 84
    }
   ],
   "source": [
    "library_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "library_df_overlap = library_df.loc[library_df[\"华大样本编号P\"].isin(sampleId_unique)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "19P0156412    9\n19P0156384    9\n19P0156413    9\n19P0156407    8\n19P0156409    8\n             ..\n19P7247876    1\n19P7354486    1\n19P5298109    1\n19P0126530    1\n19P0126601    1\nName: 华大样本编号P, Length: 1256, dtype: int64"
     },
     "metadata": {},
     "execution_count": 86
    }
   ],
   "source": [
    "library_df_overlap[\"华大样本编号P\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "19P0156384    9\n19P0156412    8\n19P0156413    8\n19P0156407    7\n19P0156409    7\n             ..\n19P7247876    1\n19P7354486    1\n19P5298109    1\n19P0126530    1\n19P0126601    1\nName: 华大样本编号P, Length: 1256, dtype: int64"
     },
     "metadata": {},
     "execution_count": 87
    }
   ],
   "source": [
    "library_df_norepeat_overlap = library_df_norepeat.loc[library_df_norepeat[\"华大样本编号P\"].isin(sampleId_unique)]\n",
    "library_df_norepeat_overlap[\"华大样本编号P\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "     管上编号    是否临床        文库名 样本标签 index列表         建库日期 建库方法 试剂批次  起始量 PCR循环数  \\\n638     7  Clinic   ssM-7-pl    P   69,70  2.01904e+07    1    无   10     12   \n1157   11  Clinic  ssM-11-pl    T   69,70  2.01907e+07  1.1    无  200     12   \n1158   12  Clinic  ssM-12-pl    T   71,72  2.01907e+07  1.1    无  200     12   \n1472    7    Test   ssM-7-pl    P   69,70  2.01904e+07    1    无   10     12   \n1516    7    Test   ssM-7-pl    P   69,70  2.01904e+07    1    无   10     12   \n1524    7    Test   ssM-7-pl    P   69,70  2.01904e+07    1    无   10     12   \n1564    7    Test   ssM-7-pl    P   69,70  2.01904e+07    1    无   10     12   \n1572    7    Test   ssM-7-pl    P   69,70  2.01904e+07    1    无   10     12   \n\n      文库浓度 文库体积  操作人                      备注      华大样本编号          任务单名称  \\\n638     64   40  CSS                       无  19P0156412   单链建库测序任务单-52   \n1157  30.8   40  CSS                       无  19L0156412     肝组织样本甲基化建库   \n1158  36.8   40  CSS                       无  19S0156412     肝组织样本甲基化建库   \n1472    64   40  CSS                   八杂一测试  19P0156412  伯科甲基化小panel测试   \n1516    64   40  CSS  杂交、M270捕获温度65℃，清洗温度65℃  19P0156412  伯科甲基化小panel测试   \n1524    64   40  CSS  杂交、M270捕获温度56℃，清洗温度65℃  19P0156412  伯科甲基化小panel测试   \n1564    64   40  CSS  伯科486K-15pmol-56-56-65  19P0156412  伯科甲基化小panel测试   \n1572    64   40  CSS  伯科486K-15pmol-56-56-56  19P0156412  伯科甲基化小panel测试   \n\n                     实验目的    样本名     华大样本编号P  \n638             肝癌血浆样本测试；    H45  19P0156412  \n1157           旁组织甲基化文库构建  H45-T  19P0156412  \n1158           旁组织甲基化文库构建  H45-P  19P0156412  \n1472  测试伯科甲基化486k小panel捕获    H45  19P0156412  \n1516  测试伯科甲基化486k小panel捕获    H45  19P0156412  \n1524  测试伯科甲基化486k小panel捕获    H45  19P0156412  \n1564  测试伯科甲基化486k小panel捕获    H45  19P0156412  \n1572  测试伯科甲基化486k小panel捕获    H45  19P0156412  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>管上编号</th>\n      <th>是否临床</th>\n      <th>文库名</th>\n      <th>样本标签</th>\n      <th>index列表</th>\n      <th>建库日期</th>\n      <th>建库方法</th>\n      <th>试剂批次</th>\n      <th>起始量</th>\n      <th>PCR循环数</th>\n      <th>文库浓度</th>\n      <th>文库体积</th>\n      <th>操作人</th>\n      <th>备注</th>\n      <th>华大样本编号</th>\n      <th>任务单名称</th>\n      <th>实验目的</th>\n      <th>样本名</th>\n      <th>华大样本编号P</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <td>638</td>\n      <td>7</td>\n      <td>Clinic</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>2.01904e+07</td>\n      <td>1</td>\n      <td>无</td>\n      <td>10</td>\n      <td>12</td>\n      <td>64</td>\n      <td>40</td>\n      <td>CSS</td>\n      <td>无</td>\n      <td>19P0156412</td>\n      <td>单链建库测序任务单-52</td>\n      <td>肝癌血浆样本测试；</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1157</td>\n      <td>11</td>\n      <td>Clinic</td>\n      <td>ssM-11-pl</td>\n      <td>T</td>\n      <td>69,70</td>\n      <td>2.01907e+07</td>\n      <td>1.1</td>\n      <td>无</td>\n      <td>200</td>\n      <td>12</td>\n      <td>30.8</td>\n      <td>40</td>\n      <td>CSS</td>\n      <td>无</td>\n      <td>19L0156412</td>\n      <td>肝组织样本甲基化建库</td>\n      <td>旁组织甲基化文库构建</td>\n      <td>H45-T</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1158</td>\n      <td>12</td>\n      <td>Clinic</td>\n      <td>ssM-12-pl</td>\n      <td>T</td>\n      <td>71,72</td>\n      <td>2.01907e+07</td>\n      <td>1.1</td>\n      <td>无</td>\n      <td>200</td>\n      <td>12</td>\n      <td>36.8</td>\n      <td>40</td>\n      <td>CSS</td>\n      <td>无</td>\n      <td>19S0156412</td>\n      <td>肝组织样本甲基化建库</td>\n      <td>旁组织甲基化文库构建</td>\n      <td>H45-P</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1472</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>2.01904e+07</td>\n      <td>1</td>\n      <td>无</td>\n      <td>10</td>\n      <td>12</td>\n      <td>64</td>\n      <td>40</td>\n      <td>CSS</td>\n      <td>八杂一测试</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1516</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>2.01904e+07</td>\n      <td>1</td>\n      <td>无</td>\n      <td>10</td>\n      <td>12</td>\n      <td>64</td>\n      <td>40</td>\n      <td>CSS</td>\n      <td>杂交、M270捕获温度65℃，清洗温度65℃</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1524</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>2.01904e+07</td>\n      <td>1</td>\n      <td>无</td>\n      <td>10</td>\n      <td>12</td>\n      <td>64</td>\n      <td>40</td>\n      <td>CSS</td>\n      <td>杂交、M270捕获温度56℃，清洗温度65℃</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1564</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>2.01904e+07</td>\n      <td>1</td>\n      <td>无</td>\n      <td>10</td>\n      <td>12</td>\n      <td>64</td>\n      <td>40</td>\n      <td>CSS</td>\n      <td>伯科486K-15pmol-56-56-65</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1572</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>2.01904e+07</td>\n      <td>1</td>\n      <td>无</td>\n      <td>10</td>\n      <td>12</td>\n      <td>64</td>\n      <td>40</td>\n      <td>CSS</td>\n      <td>伯科486K-15pmol-56-56-56</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 88
    }
   ],
   "source": [
    "tmp = library_df_norepeat_overlap[library_df_norepeat_overlap[\"华大样本编号P\"]==\"19P0156412\"]\n",
    "tmp.iloc[:,2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "管上编号                            7\n是否临床                         Test\n文库名                      ssM-7-pl\n样本标签                            P\nindex列表                     69,70\n建库日期                  2.01904e+07\n建库方法                            1\n试剂批次                            无\n起始量                            10\nPCR循环数                         12\n文库浓度                           64\n文库体积                           40\n操作人                           CSS\n备注         杂交、M270捕获温度65℃，清洗温度65℃\n华大样本编号                 19P0156412\n任务单名称               伯科甲基化小panel测试\n实验目的          测试伯科甲基化486k小panel捕获\n样本名                           H45\n华大样本编号P                19P0156412\nName: 1516, dtype: object"
     },
     "metadata": {},
     "execution_count": 89
    }
   ],
   "source": [
    "tmp.drop_duplicates([\"管上编号\", \"是否临床\", \"文库名\", \"样本标签\", \"index列表\", \"建库日期\", \"建库方法\", \"试剂批次\", \"起始量\", \"PCR循环数\", \"文库浓度\", \"文库体积\", \"操作人\", \"备注\", \"任务单名称\", \"实验目的\", \"样本名\"]).iloc[4,2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "管上编号                            7\n是否临床                         Test\n文库名                      ssM-7-pl\n样本标签                            P\nindex列表                     69,70\n建库日期                  2.01904e+07\n建库方法                            1\n试剂批次                            无\n起始量                            10\nPCR循环数                         12\n文库浓度                           64\n文库体积                           40\n操作人                           CSS\n备注         杂交、M270捕获温度56℃，清洗温度65℃\n华大样本编号                 19P0156412\n任务单名称               伯科甲基化小panel测试\n实验目的          测试伯科甲基化486k小panel捕获\n样本名                           H45\n华大样本编号P                19P0156412\nName: 1524, dtype: object"
     },
     "metadata": {},
     "execution_count": 90
    }
   ],
   "source": [
    "tmp.drop_duplicates([\"管上编号\", \"是否临床\", \"文库名\", \"样本标签\", \"index列表\", \"建库日期\", \"建库方法\", \"试剂批次\", \"起始量\", \"PCR循环数\", \"文库浓度\", \"文库体积\", \"操作人\", \"备注\", \"任务单名称\", \"实验目的\", \"样本名\"]).iloc[5,2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "管上编号        True\n是否临床        True\n文库名         True\n样本标签        True\nindex列表     True\n建库日期        True\n建库方法        True\n试剂批次        True\n起始量         True\nPCR循环数      True\n文库浓度        True\n文库体积        True\n操作人         True\n备注         False\n华大样本编号      True\n任务单名称       True\n实验目的        True\n样本名         True\n华大样本编号P     True\ndtype: bool"
     },
     "metadata": {},
     "execution_count": 92
    }
   ],
   "source": [
    "tmp.drop_duplicates([\"管上编号\", \"是否临床\", \"文库名\", \"样本标签\", \"index列表\", \"建库日期\", \"建库方法\", \"试剂批次\", \"起始量\", \"PCR循环数\", \"文库浓度\", \"文库体积\", \"操作人\", \"备注\", \"任务单名称\", \"实验目的\", \"样本名\"]).iloc[5,2:]==tmp.drop_duplicates([\"管上编号\", \"是否临床\", \"文库名\", \"样本标签\", \"index列表\", \"建库日期\", \"建库方法\", \"试剂批次\", \"起始量\", \"PCR循环数\", \"文库浓度\", \"文库体积\", \"操作人\", \"备注\", \"任务单名称\", \"实验目的\", \"样本名\"]).iloc[4,2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "1516    NaN\n1524    NaN\nName: 试剂批次, dtype: object"
     },
     "metadata": {},
     "execution_count": 75
    }
   ],
   "source": [
    "tmp.drop_duplicates([\"管上编号\", \"是否临床\", \"文库名\", \"样本标签\", \"index列表\", \"建库日期\", \"建库方法\", \"试剂批次\", \"起始量\", \"PCR循环数\", \"文库浓度\", \"文库体积\", \"操作人\", \"备注\", \"任务单名称\", \"实验目的\", \"样本名\"]).iloc[4:6,9]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "      建库编号  DNA提取编号 管上编号    是否临床        文库名 样本标签 index列表        建库日期 建库方法  \\\n638    NaN      NaN    7  Clinic   ssM-7-pl    P   69,70  20190402.0    1   \n1157   NaN      NaN   11  Clinic  ssM-11-pl    T   69,70  20190716.0  1.1   \n1158   NaN      NaN   12  Clinic  ssM-12-pl    T   71,72  20190716.0  1.1   \n1472   NaN      NaN    7    Test   ssM-7-pl    P   69,70  20190402.0    1   \n1516   NaN      NaN    7    Test   ssM-7-pl    P   69,70  20190402.0    1   \n1524   NaN      NaN    7    Test   ssM-7-pl    P   69,70  20190402.0    1   \n1564   NaN      NaN    7    Test   ssM-7-pl    P   69,70  20190402.0    1   \n1572   NaN      NaN    7    Test   ssM-7-pl    P   69,70  20190402.0    1   \n1580   NaN      NaN    7    Test   ssM-7-pl    P   69,70  20190402.0    1   \n\n     试剂批次  ...  PCR循环数  文库浓度  文库体积  操作人                      备注      华大样本编号  \\\n638   NaN  ...    12.0    64  40.0  CSS                     NaN  19P0156412   \n1157  NaN  ...    12.0  30.8  40.0  CSS                     NaN  19L0156412   \n1158  NaN  ...    12.0  36.8  40.0  CSS                     NaN  19S0156412   \n1472  NaN  ...    12.0    64  40.0  CSS                   八杂一测试  19P0156412   \n1516  NaN  ...    12.0    64  40.0  CSS  杂交、M270捕获温度65℃，清洗温度65℃  19P0156412   \n1524  NaN  ...    12.0    64  40.0  CSS  杂交、M270捕获温度56℃，清洗温度65℃  19P0156412   \n1564  NaN  ...    12.0    64  40.0  CSS  伯科486K-15pmol-56-56-65  19P0156412   \n1572  NaN  ...    12.0    64  40.0  CSS  伯科486K-15pmol-56-56-56  19P0156412   \n1580  NaN  ...    12.0    64  40.0  CSS  伯科486K-15pmol-56-56-56  19P0156412   \n\n              任务单名称                 实验目的    样本名     华大样本编号P  \n638    单链建库测序任务单-52            肝癌血浆样本测试；    H45  19P0156412  \n1157     肝组织样本甲基化建库           旁组织甲基化文库构建  H45-T  19P0156412  \n1158     肝组织样本甲基化建库           旁组织甲基化文库构建  H45-P  19P0156412  \n1472  伯科甲基化小panel测试  测试伯科甲基化486k小panel捕获    H45  19P0156412  \n1516  伯科甲基化小panel测试  测试伯科甲基化486k小panel捕获    H45  19P0156412  \n1524  伯科甲基化小panel测试  测试伯科甲基化486k小panel捕获    H45  19P0156412  \n1564  伯科甲基化小panel测试  测试伯科甲基化486k小panel捕获    H45  19P0156412  \n1572  伯科甲基化小panel测试  测试伯科甲基化486k小panel捕获    H45  19P0156412  \n1580  伯科甲基化小panel测试  测试伯科甲基化486k小panel捕获    H45  19P0156412  \n\n[9 rows x 21 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>建库编号</th>\n      <th>DNA提取编号</th>\n      <th>管上编号</th>\n      <th>是否临床</th>\n      <th>文库名</th>\n      <th>样本标签</th>\n      <th>index列表</th>\n      <th>建库日期</th>\n      <th>建库方法</th>\n      <th>试剂批次</th>\n      <th>...</th>\n      <th>PCR循环数</th>\n      <th>文库浓度</th>\n      <th>文库体积</th>\n      <th>操作人</th>\n      <th>备注</th>\n      <th>华大样本编号</th>\n      <th>任务单名称</th>\n      <th>实验目的</th>\n      <th>样本名</th>\n      <th>华大样本编号P</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <td>638</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>7</td>\n      <td>Clinic</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>20190402.0</td>\n      <td>1</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>12.0</td>\n      <td>64</td>\n      <td>40.0</td>\n      <td>CSS</td>\n      <td>NaN</td>\n      <td>19P0156412</td>\n      <td>单链建库测序任务单-52</td>\n      <td>肝癌血浆样本测试；</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1157</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>11</td>\n      <td>Clinic</td>\n      <td>ssM-11-pl</td>\n      <td>T</td>\n      <td>69,70</td>\n      <td>20190716.0</td>\n      <td>1.1</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>12.0</td>\n      <td>30.8</td>\n      <td>40.0</td>\n      <td>CSS</td>\n      <td>NaN</td>\n      <td>19L0156412</td>\n      <td>肝组织样本甲基化建库</td>\n      <td>旁组织甲基化文库构建</td>\n      <td>H45-T</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1158</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>12</td>\n      <td>Clinic</td>\n      <td>ssM-12-pl</td>\n      <td>T</td>\n      <td>71,72</td>\n      <td>20190716.0</td>\n      <td>1.1</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>12.0</td>\n      <td>36.8</td>\n      <td>40.0</td>\n      <td>CSS</td>\n      <td>NaN</td>\n      <td>19S0156412</td>\n      <td>肝组织样本甲基化建库</td>\n      <td>旁组织甲基化文库构建</td>\n      <td>H45-P</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1472</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>20190402.0</td>\n      <td>1</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>12.0</td>\n      <td>64</td>\n      <td>40.0</td>\n      <td>CSS</td>\n      <td>八杂一测试</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1516</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>20190402.0</td>\n      <td>1</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>12.0</td>\n      <td>64</td>\n      <td>40.0</td>\n      <td>CSS</td>\n      <td>杂交、M270捕获温度65℃，清洗温度65℃</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1524</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>20190402.0</td>\n      <td>1</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>12.0</td>\n      <td>64</td>\n      <td>40.0</td>\n      <td>CSS</td>\n      <td>杂交、M270捕获温度56℃，清洗温度65℃</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1564</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>20190402.0</td>\n      <td>1</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>12.0</td>\n      <td>64</td>\n      <td>40.0</td>\n      <td>CSS</td>\n      <td>伯科486K-15pmol-56-56-65</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1572</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>20190402.0</td>\n      <td>1</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>12.0</td>\n      <td>64</td>\n      <td>40.0</td>\n      <td>CSS</td>\n      <td>伯科486K-15pmol-56-56-56</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n    <tr>\n      <td>1580</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>7</td>\n      <td>Test</td>\n      <td>ssM-7-pl</td>\n      <td>P</td>\n      <td>69,70</td>\n      <td>20190402.0</td>\n      <td>1</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>12.0</td>\n      <td>64</td>\n      <td>40.0</td>\n      <td>CSS</td>\n      <td>伯科486K-15pmol-56-56-56</td>\n      <td>19P0156412</td>\n      <td>伯科甲基化小panel测试</td>\n      <td>测试伯科甲基化486k小panel捕获</td>\n      <td>H45</td>\n      <td>19P0156412</td>\n    </tr>\n  </tbody>\n</table>\n<p>9 rows × 21 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "source": [
    "library_df_overlap[library_df_overlap[\"华大样本编号P\"]==\"19P0156412\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx in range(df.shape[0]):\n",
    "    k1 = \"无\" if pd.isna(df[\"员工姓名\"][idx]) or pd.isnull(df[\"员工姓名\"][idx]) else df[\"员工姓名\"][idx]\n",
    "    k2 = \"无\" if pd.isna(df[\"手机号\"][idx]) or pd.isnull(df[\"手机号\"][idx]) else df[\"手机号\"][idx]\n",
    "    k3 = \"无\" if pd.isna(df[\"给谁报名\"][idx]) or pd.isnull(df[\"给谁报名\"][idx]) else df[\"给谁报名\"][idx]\n",
    "    k4 = \"无\" if pd.isna(df[\"电子邮箱地址\"][idx]) or pd.isnull(df[\"电子邮箱地址\"][idx]) else df[\"电子邮箱地址\"][idx]\n",
    "    k5 = \"无\" if pd.isna(df[\"备注\"][idx]) or pd.isnull(df[\"备注\"][idx]) else df[\"备注\"][idx]\n",
    "    print(\"员工姓名: {}, 手机号: {}, 给谁报名: {}, 电子邮箱地址: {}, 备注: {}\".format(k1, k2, k3, k4, k5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.isna(df[\"备注\"][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx in range(df.shape[0]):\n",
    "    k1 = \"无\" if pd.isna(df[\"员工姓名\"][idx]) or pd.isnull(df[\"员工姓名\"][idx]) else df[\"员工姓名\"][idx]\n",
    "    k2 = \"无\" if pd.isna(df[\"手机号\"][idx]) or pd.isnull(df[\"手机号\"][idx]) else df[\"手机号\"][idx]\n",
    "    k3 = \"无\" if pd.isna(df[\"给谁报名\"][idx]) or pd.isnull(df[\"给谁报名\"][idx]) else df[\"给谁报名\"][idx]\n",
    "    k4 = \"无\" if pd.isna(df[\"电子邮箱地址\"][idx]) or pd.isnull(df[\"电子邮箱地址\"][idx]) else df[\"电子邮箱地址\"][idx]\n",
    "    k5 = \"无\" if pd.isna(df[\"备注\"][idx]) or pd.isnull(df[\"备注\"][idx]) else df[\"备注\"][idx]\n",
    "    print(\"员工姓名: {}, 手机号: {}, 给谁报名: {}, 电子邮箱地址: {}, 备注: {}\".format(k1, k2, k3, k4, k5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_excel(\"广州南医五院第一批样本 20190905.xlsx\")\n",
    "for idx in range(df.shape[0]):\n",
    "    k1 = \"无\" if pd.isna(df[\"采血试管号\"][idx]) or pd.isnull(df[\"采血试管号\"][idx]) else df[\"采血试管号\"][idx]\n",
    "    k2 = \"{}, {}\".format(df[\"是否有家族肠道病\"][idx], df[\"何种家族肠道病\"][idx]) if df[\"是否有家族肠道病\"][idx]==\"是\" and not pd.isna(df[\"何种家族肠道病\"][idx]) else df[\"是否有家族肠道病\"][idx]\n",
    "    k3 = \"{}, {}\".format(df[\"个人是否患肠道病\"][idx], df[\"何种肠道病\"][idx]) if df[\"个人是否患肠道病\"][idx]==\"是\" and not pd.isna(df[\"何种肠道病\"][idx]) else df[\"个人是否患肠道病\"][idx]\n",
    "    k4 = \"{}, 结果{}\".format(df[\"是否做过肠镜筛查检测\"][idx], df[\"肠镜筛查结果\"][idx]) if df[\"是否做过肠镜筛查检测\"][idx]==\"是\" and not pd.isna(df[\"肠镜筛查结果\"][idx]) else df[\"是否做过肠镜筛查检测\"][idx]\n",
    "    k5 = \"{}, 结果{}\".format(df[\"是否做过粪便隐血检测\"][idx], df[\"粪便隐血结果\"][idx]) if df[\"是否做过粪便隐血检测\"][idx]==\"是\" and not pd.isna(df[\"粪便隐血结果\"][idx]) else df[\"是否做过粪便隐血检测\"][idx]\n",
    "    k6 = \"无\" if pd.isna(df[\"其他方法肠道检查结果\"][idx]) or pd.isnull(df[\"其他方法肠道检查结果\"][idx]) else df[\"其他方法肠道检查结果\"][idx]\n",
    "    k7 = \"无\" if pd.isna(df[\"是否便血\"][idx]) or pd.isnull(df[\"是否便血\"][idx]) else df[\"是否便血\"][idx]\n",
    "    k8 = \"无\" if pd.isna(df[\"是否经常腹痛腹胀\"][idx]) or pd.isnull(df[\"是否经常腹痛腹胀\"][idx]) else df[\"是否经常腹痛腹胀\"][idx]\n",
    "    k9 = \"无\" if pd.isna(df[\"是否便秘\"][idx]) or pd.isnull(df[\"是否便秘\"][idx]) else df[\"是否便秘\"][idx]\n",
    "    k10 = \"无\" if pd.isna(df[\"日常大便次数\"][idx]) or pd.isnull(df[\"日常大便次数\"][idx]) else df[\"日常大便次数\"][idx]\n",
    "    k11 = \"无\" if pd.isna(df[\"饮食习惯\"][idx]) or pd.isnull(df[\"饮食习惯\"][idx]) else df[\"饮食习惯\"][idx]\n",
    "    print(\"采血试管号: {}, 是否有家族肠道病: {}, 个人是否患肠道病: {}, 是否做过肠镜筛查检测: {}, 是否做过粪便隐血检测: {}, 其他方法肠道检查结果: {}, 是否便血: {}, 是否经常腹痛腹胀: {}, 是否便秘: {}, 日常大便次数: {}, 饮食习惯: {}\".format(k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df.loc[[46]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.listdir(\"./\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_excel(\"湖南省肿瘤医院结直肠癌 20191107.xlsx\")\n",
    "for idx in range(df.shape[0]):\n",
    "    k1 = \"无\" if pd.isna(df[\"是否行放化疗\"][idx]) or pd.isnull(df[\"是否行放化疗\"][idx]) or df[\"是否行放化疗\"][idx]==\"/\"  else df[\"是否行放化疗\"][idx]\n",
    "    k2 = \"无\" if pd.isna(df[\"病理号\"][idx]) or pd.isnull(df[\"病理号\"][idx]) or df[\"病理号\"][idx]==\"/\" else df[\"病理号\"][idx]\n",
    "    k3 = \"(无)\" if pd.isna(df[\"T\"][idx]) or pd.isnull(df[\"T\"][idx]) or df[\"T\"][idx]==\"/\" else str(df[\"T\"][idx])[0]\n",
    "    k4 = \"(无)\" if pd.isna(df[\"N\"][idx]) or pd.isnull(df[\"N\"][idx]) or df[\"N\"][idx]==\"/\" else str(df[\"N\"][idx])[0]\n",
    "    k5 = \"(无)\" if pd.isna(df[\"M\"][idx]) or pd.isnull(df[\"M\"][idx]) or df[\"M\"][idx]==\"/\" else str(df[\"M\"][idx])[0]\n",
    "    k6 = \"无\" if pd.isna(df[\"肿块大小\"][idx]) or pd.isnull(df[\"肿块大小\"][idx]) or df[\"肿块大小\"][idx]==\"/\" else df[\"肿块大小\"][idx]\n",
    "    k7 = \"无\" if pd.isna(df[\"免疫组化结果\"][idx]) or pd.isnull(df[\"免疫组化结果\"][idx]) or df[\"免疫组化结果\"][idx]==\"/\" else df[\"免疫组化结果\"][idx]\n",
    "    k8 = \"无\" if pd.isna(df[\"肠镜结果\"][idx]) or pd.isnull(df[\"肠镜结果\"][idx]) or df[\"肠镜结果\"][idx]==\"/\" else df[\"肠镜结果\"][idx]\n",
    "    k9 = \"无\" if pd.isna(df[\"备注\"][idx]) or pd.isnull(df[\"备注\"][idx]) or df[\"备注\"][idx]==\"/\" else df[\"备注\"][idx]\n",
    "    print(\"是否行放化疗: {}; 病理号: {}; T{}; N{}; M{}; 肿块大小: {}; 免疫组化结果: {}; 肠镜结果: {}; 备注: {}\".format(k1, k2, k3, k4, k5, k6, k7, k8, k9))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"备注\"][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_count(s, df, idx):\n",
    "\n",
    "    if \"P\" in s:\n",
    "        df[\"血浆管数\"][idx] = 1\n",
    "    elif \"L\" in s:\n",
    "        df[\"癌组织\"][idx] = 1\n",
    "    elif \"S\" in s:\n",
    "        df[\"癌旁组织\"][idx] = 1\n",
    "    elif \"B\" in s:\n",
    "        df[\"白细胞\"][idx] = 1\n",
    "    elif \"T\" in s:\n",
    "        df[\"粪便\"][idx] = 1\n",
    "\n",
    "def add_row_extract_df(extract_df, sample_id, dna_id):\n",
    "    extract_df[\"样本编号\"].append(sample_id)\n",
    "    extract_df[\"DNA编号\"].append(dna_id)\n",
    "    if \"P\" in dna_id:\n",
    "        extract_df[\"样本类型\"].append(\"血浆\")\n",
    "    elif \"L\" in dna_id:\n",
    "        extract_df[\"样本类型\"].append(\"癌组织\")\n",
    "    elif \"S\" in dna_id:\n",
    "        extract_df[\"样本类型\"].append(\"癌旁组织\")\n",
    "    elif \"B\" in dna_id:\n",
    "        extract_df[\"样本类型\"].append(\"白细胞\")\n",
    "    elif \"T\" in dna_id:\n",
    "        extract_df[\"样本类型\"].append(\"粪便\")\n",
    "    else:\n",
    "        extract_df[\"样本类型\"].append(\"其他\")\n",
    "\n",
    "\n",
    "def data_process(df):\n",
    "\n",
    "    # 输入df: \"华大编号\", \"甲基化文库名\", \"Oseq-S文库\", \"白细胞突变文库\"\n",
    "    # 输出\n",
    "    # 1 临床信息表: \"样本编号\", \"血浆管数\", \"癌旁组织\", \"癌组织\", \"白细胞\", \"粪便\"\n",
    "    # 2 提取信息表: \"样本编号\", \"DNA编号\", \"样本类型\"\n",
    "    # 3 DNA使用记录表: \"DNA编号\", \"备注\"\n",
    "    # 4 甲基化建库表: \"DNA编号\", \"建库编号\", \"文库名\"\n",
    "\n",
    "    clinical_df = { \"样本编号\": [], \"血浆管数\": [], \"癌旁组织\": [], \"癌组织\": [], \"白细胞\": [], \"粪便\": [] }\n",
    "    extract_df = { \"DNA编号\": [], \"样本编号\": [], \"样本类型\": [] }\n",
    "    usage_df = { \"DNA编号\": [], \"备注\": []  }\n",
    "    library_df = {  \"建库编号\": [], \"DNA编号\": [], \"文库名\": [] }\n",
    "    for idx in range(len(df[\"华大编号\"])):\n",
    "        sample_id = df[\"华大编号\"][idx][:2] + \"P\" + df[\"华大编号\"][idx][3:]\n",
    "        clinical_df[\"样本编号\"].append(sample_id)\n",
    "        clinical_df[\"血浆管数\"].append(0)\n",
    "        clinical_df[\"癌旁组织\"].append(0)\n",
    "        clinical_df[\"癌组织\"].append(0)\n",
    "        clinical_df[\"白细胞\"].append(0)\n",
    "        clinical_df[\"粪便\"].append(0)\n",
    "        add_count(df[\"华大编号\"][idx], clinical_df, idx)\n",
    "\n",
    "        if df[\"甲基化文库名\"][idx]:\n",
    "            dna_id = df[\"甲基化文库名\"][idx][:-1]+\"_1\"\n",
    "            library_id = dna_id+\"_m1\"\n",
    "            add_row_extract_df(extract_df, sample_id, dna_id)\n",
    "            library_df[\"DNA编号\"].append(dna_id)\n",
    "            library_df[\"建库编号\"].append(library_id)\n",
    "            library_df[\"文库名\"].append(df[\"甲基化文库名\"][idx])\n",
    "            add_count(df[\"甲基化文库名\"][idx], clinical_df, idx)\n",
    "    \n",
    "    for idx in range(len(df[\"华大编号\"])):\n",
    "        if df[\"Oseq-S文库\"][idx]:\n",
    "            sample_id = df[\"华大编号\"][idx][:2] + \"P\" + df[\"华大编号\"][idx][3:]\n",
    "            idx2=df[\"Oseq-S文库\"][idx].rfind(\"S\")\n",
    "            dna_id = df[\"Oseq-S文库\"][idx][:idx2]+\"_1\"\n",
    "            if dna_id not in extract_df[\"DNA编号\"]:\n",
    "                add_row_extract_df(extract_df, sample_id, dna_id)\n",
    "                add_count(df[\"Oseq-S文库\"][idx], clinical_df, idx)\n",
    "            usage_df[\"DNA编号\"].append(dna_id)\n",
    "            usage_df[\"备注\"].append(\"Oseq-S文库建库成功, Oseq-S文库编号: \"+df[\"Oseq-S文库\"][idx])\n",
    "\n",
    "    for idx in range(len(df[\"华大编号\"])):\n",
    "        if df[\"白细胞突变文库\"][idx]:\n",
    "            sample_id = df[\"华大编号\"][idx][:2] + \"P\" + df[\"华大编号\"][idx][3:]\n",
    "            idx2=df[\"白细胞突变文库\"][idx].rfind(\"S\")\n",
    "            dna_id = df[\"白细胞突变文库\"][idx][:idx2]+\"_1\"\n",
    "            add_row_extract_df(extract_df, sample_id, dna_id)\n",
    "            clinical_df[\"白细胞\"][idx] = 1\n",
    "            usage_df[\"DNA编号\"].append(dna_id)\n",
    "            usage_df[\"备注\"].append(\"Oseq-S文库建库成功, Oseq-S文库编号: \"+df[\"白细胞突变文库\"][idx])\n",
    "\n",
    "    return clinical_df, extract_df, usage_df, library_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df = pd.read_excel(\"广州南医五院第二批样本 20191119.xlsx\")\n",
    "raw_df = raw_df[ ( (pd.notna(raw_df[\"华大编号\"])) & (pd.notnull(raw_df[\"华大编号\"])) )] \n",
    "data_df = {\n",
    "    \"华大编号\": list(raw_df[\"华大编号\"]), \n",
    "    \"甲基化文库名\": [ \"\" if pd.isna(raw_df[\"甲基化文库\"][idx]) or pd.isnull(raw_df[\"甲基化文库\"][idx]) else raw_df[\"甲基化文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ],\n",
    "    \"Oseq-S文库\": [ \"\" if pd.isna(raw_df[\"Oseq-S文库\"][idx]) or pd.isnull(raw_df[\"Oseq-S文库\"][idx]) else raw_df[\"Oseq-S文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ], \n",
    "    # \"白细胞突变文库\": [ \"\"  for idx in range(len(raw_df[\"华大编号\"])) ]\n",
    "    \"白细胞突变文库\": [ \"\" if pd.isna(raw_df[\"白细胞文库\"][idx]) or pd.isnull(raw_df[\"白细胞文库\"][idx]) else raw_df[\"白细胞文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ]\n",
    "}\n",
    "clinical_df, extract_df, usage_df, library_df = data_process(data_df)\n",
    "with pd.ExcelWriter(\"test.info.xlsx\") as writer:\n",
    "    pd.DataFrame(clinical_df).to_excel(writer, index=False, sheet_name='clinical')\n",
    "    pd.DataFrame(extract_df).to_excel(writer, index=False, sheet_name='extract')\n",
    "    pd.DataFrame(usage_df).to_excel(writer, index=False, sheet_name='usage')\n",
    "    pd.DataFrame(library_df).to_excel(writer, index=False, sheet_name='library')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def data_process2(df):\n",
    "\n",
    "    # 输入df: \"华大编号\", \"甲基化文库名\", \"Oseq-S文库\", \"WGS文库\"\n",
    "    # 输出\n",
    "    # 1 临床信息表: \"样本编号\", \"血浆管数\", \"癌旁组织\", \"癌组织\", \"白细胞\", \"粪便\"\n",
    "    # 2 提取信息表: \"样本编号\", \"DNA编号\", \"样本类型\"\n",
    "    # 3 DNA使用记录表: \"DNA编号\", \"备注\"\n",
    "    # 4 甲基化建库表: \"DNA编号\", \"建库编号\", \"文库名\"\n",
    "\n",
    "    clinical_df = { \"样本编号\": [], \"血浆管数\": [], \"癌旁组织\": [], \"癌组织\": [], \"白细胞\": [], \"粪便\": [] }\n",
    "    extract_df = { \"DNA编号\": [], \"样本编号\": [], \"样本类型\": [] }\n",
    "    usage_df = { \"DNA编号\": [], \"备注\": []  }\n",
    "    library_df = {  \"建库编号\": [], \"DNA编号\": [], \"文库名\": [] }\n",
    "    for idx in range(len(df[\"华大编号\"])):\n",
    "        sample_id = df[\"华大编号\"][idx][:2] + \"P\" + df[\"华大编号\"][idx][3:]\n",
    "        clinical_df[\"样本编号\"].append(sample_id)\n",
    "        clinical_df[\"血浆管数\"].append(0)\n",
    "        clinical_df[\"癌旁组织\"].append(0)\n",
    "        clinical_df[\"癌组织\"].append(0)\n",
    "        clinical_df[\"白细胞\"].append(0)\n",
    "        clinical_df[\"粪便\"].append(0)\n",
    "        add_count(df[\"华大编号\"][idx], clinical_df, idx)\n",
    "\n",
    "        if df[\"甲基化文库名\"][idx]:\n",
    "            dna_id = df[\"甲基化文库名\"][idx][:-1]+\"_1\"\n",
    "            library_id = dna_id+\"_m1\"\n",
    "            add_row_extract_df(extract_df, sample_id, dna_id)\n",
    "            library_df[\"DNA编号\"].append(dna_id)\n",
    "            library_df[\"建库编号\"].append(library_id)\n",
    "            library_df[\"文库名\"].append(df[\"甲基化文库名\"][idx])\n",
    "            add_count(df[\"甲基化文库名\"][idx], clinical_df, idx)\n",
    "    \n",
    "    for idx in range(len(df[\"华大编号\"])):\n",
    "        if df[\"Oseq-S文库\"][idx]:\n",
    "            sample_id = df[\"华大编号\"][idx][:2] + \"P\" + df[\"华大编号\"][idx][3:]\n",
    "            idx2=df[\"Oseq-S文库\"][idx].rfind(\"S\")\n",
    "            dna_id = df[\"Oseq-S文库\"][idx][:idx2]+\"_1\"\n",
    "            if dna_id not in extract_df[\"DNA编号\"]:\n",
    "                add_row_extract_df(extract_df, sample_id, dna_id)\n",
    "                add_count(df[\"Oseq-S文库\"][idx], clinical_df, idx)\n",
    "            usage_df[\"DNA编号\"].append(dna_id)\n",
    "            usage_df[\"备注\"].append(\"Oseq-S文库建库成功, Oseq-S文库编号: \"+df[\"Oseq-S文库\"][idx])\n",
    "\n",
    "    for idx in range(len(df[\"华大编号\"])):\n",
    "        if df[\"WGS文库\"][idx]:\n",
    "            sample_id = df[\"华大编号\"][idx][:2] + \"P\" + df[\"华大编号\"][idx][3:]\n",
    "            idx2=df[\"WGS文库\"][idx].rfind(\"G\")\n",
    "            dna_id = df[\"WGS文库\"][idx][:idx2]+\"_1\"\n",
    "            if dna_id not in extract_df[\"DNA编号\"]:\n",
    "                add_row_extract_df(extract_df, sample_id, dna_id)\n",
    "                add_count(df[\"WGS文库\"][idx], clinical_df, idx)\n",
    "            usage_df[\"DNA编号\"].append(dna_id)\n",
    "            usage_df[\"备注\"].append(\"WGS文库建库成功, WGS文库编号: \"+df[\"WGS文库\"][idx])\n",
    "\n",
    "    return clinical_df, extract_df, usage_df, library_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df = pd.read_excel(\"湖南省肿瘤医院结直肠癌 20191107.xlsx\")\n",
    "raw_df = raw_df[ ( (pd.notna(raw_df[\"华大编号\"])) & (pd.notnull(raw_df[\"华大编号\"])) )] \n",
    "data_df = {\n",
    "    \"华大编号\": list(raw_df[\"华大编号\"]), \n",
    "    \"甲基化文库名\": [ \"\" if pd.isna(raw_df[\"甲基化文库\"][idx]) or pd.isnull(raw_df[\"甲基化文库\"][idx]) else raw_df[\"甲基化文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ],\n",
    "    \"Oseq-S文库\": [ \"\" if pd.isna(raw_df[\"Oseq-S文库\"][idx]) or pd.isnull(raw_df[\"Oseq-S文库\"][idx]) else raw_df[\"Oseq-S文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ], \n",
    "    # \"白细胞突变文库\": [ \"\"  for idx in range(len(raw_df[\"华大编号\"])) ]\n",
    "    \"WGS文库\": [ \"\" if pd.isna(raw_df[\"WGS文库\"][idx]) or pd.isnull(raw_df[\"WGS文库\"][idx]) else raw_df[\"WGS文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ]\n",
    "}\n",
    "clinical_df, extract_df, usage_df, library_df = data_process2(data_df)\n",
    "with pd.ExcelWriter(\"test2.info.xlsx\") as writer:\n",
    "    pd.DataFrame(clinical_df).to_excel(writer, index=False, sheet_name='clinical')\n",
    "    pd.DataFrame(extract_df).to_excel(writer, index=False, sheet_name='extract')\n",
    "    pd.DataFrame(usage_df).to_excel(writer, index=False, sheet_name='usage')\n",
    "    pd.DataFrame(library_df).to_excel(writer, index=False, sheet_name='library')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def data_process3(df):\n",
    "\n",
    "    # 输入df: \"华大编号\", \"甲基化文库名\", \"Oseq-S文库\", \"WGS文库\", \"白细胞突变文库\"\n",
    "    # 输出\n",
    "    # 1 临床信息表: \"样本编号\", \"血浆管数\", \"癌旁组织\", \"癌组织\", \"白细胞\", \"粪便\"\n",
    "    # 2 提取信息表: \"样本编号\", \"DNA编号\", \"样本类型\"\n",
    "    # 3 DNA使用记录表: \"DNA编号\", \"备注\"\n",
    "    # 4 甲基化建库表: \"DNA编号\", \"建库编号\", \"文库名\"\n",
    "\n",
    "    clinical_df = { \"样本编号\": [], \"血浆管数\": [], \"癌旁组织\": [], \"癌组织\": [], \"白细胞\": [], \"粪便\": [] }\n",
    "    extract_df = { \"DNA编号\": [], \"样本编号\": [], \"样本类型\": [] }\n",
    "    usage_df = { \"DNA编号\": [], \"备注\": []  }\n",
    "    library_df = {  \"建库编号\": [], \"DNA编号\": [], \"文库名\": [] }\n",
    "    for idx in range(len(df[\"华大编号\"])):\n",
    "        sample_id = df[\"华大编号\"][idx][:2] + \"P\" + df[\"华大编号\"][idx][3:]\n",
    "        clinical_df[\"样本编号\"].append(sample_id)\n",
    "        clinical_df[\"血浆管数\"].append(0)\n",
    "        clinical_df[\"癌旁组织\"].append(0)\n",
    "        clinical_df[\"癌组织\"].append(0)\n",
    "        clinical_df[\"白细胞\"].append(0)\n",
    "        clinical_df[\"粪便\"].append(0)\n",
    "        add_count(df[\"华大编号\"][idx], clinical_df, idx)\n",
    "\n",
    "        if df[\"甲基化文库名\"][idx]:\n",
    "            dna_id = df[\"甲基化文库名\"][idx][:-1]+\"_1\"\n",
    "            library_id = dna_id+\"_m1\"\n",
    "            add_row_extract_df(extract_df, sample_id, dna_id)\n",
    "            library_df[\"DNA编号\"].append(dna_id)\n",
    "            library_df[\"建库编号\"].append(library_id)\n",
    "            library_df[\"文库名\"].append(df[\"甲基化文库名\"][idx])\n",
    "            add_count(df[\"甲基化文库名\"][idx], clinical_df, idx)\n",
    "    \n",
    "    for idx in range(len(df[\"华大编号\"])):\n",
    "        if df[\"Oseq-S文库\"][idx]:\n",
    "            sample_id = df[\"华大编号\"][idx][:2] + \"P\" + df[\"华大编号\"][idx][3:]\n",
    "            idx2=df[\"Oseq-S文库\"][idx].rfind(\"S\")\n",
    "            dna_id = df[\"Oseq-S文库\"][idx][:idx2]+\"_1\"\n",
    "            if dna_id not in extract_df[\"DNA编号\"]:\n",
    "                add_row_extract_df(extract_df, sample_id, dna_id)\n",
    "                add_count(df[\"Oseq-S文库\"][idx], clinical_df, idx)\n",
    "            usage_df[\"DNA编号\"].append(dna_id)\n",
    "            usage_df[\"备注\"].append(\"Oseq-S文库建库成功, Oseq-S文库编号: \"+df[\"Oseq-S文库\"][idx])\n",
    "\n",
    "    for idx in range(len(df[\"华大编号\"])):\n",
    "        if df[\"WGS文库\"][idx]:\n",
    "            sample_id = df[\"华大编号\"][idx][:2] + \"P\" + df[\"华大编号\"][idx][3:]\n",
    "            idx2=df[\"WGS文库\"][idx].rfind(\"G\")\n",
    "            dna_id = df[\"WGS文库\"][idx][:idx2]+\"_1\"\n",
    "            if dna_id not in extract_df[\"DNA编号\"]:\n",
    "                add_row_extract_df(extract_df, sample_id, dna_id)\n",
    "                add_count(df[\"WGS文库\"][idx], clinical_df, idx)\n",
    "            usage_df[\"DNA编号\"].append(dna_id)\n",
    "            usage_df[\"备注\"].append(\"WGS文库建库成功, WGS文库编号: \"+df[\"WGS文库\"][idx])\n",
    "\n",
    "    for idx in range(len(df[\"华大编号\"])):\n",
    "        if df[\"白细胞突变文库\"][idx]:\n",
    "            sample_id = df[\"华大编号\"][idx][:2] + \"P\" + df[\"华大编号\"][idx][3:]\n",
    "            idx2=df[\"白细胞突变文库\"][idx].rfind(\"S\")\n",
    "            dna_id = df[\"白细胞突变文库\"][idx][:idx2]+\"_1\"\n",
    "            add_row_extract_df(extract_df, sample_id, dna_id)\n",
    "            clinical_df[\"白细胞\"][idx] = 1\n",
    "            usage_df[\"DNA编号\"].append(dna_id)\n",
    "            usage_df[\"备注\"].append(\"Oseq-S文库建库成功, Oseq-S文库编号: \"+df[\"白细胞突变文库\"][idx])\n",
    "\n",
    "    return clinical_df, extract_df, usage_df, library_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df = pd.read_excel(\"上海复旦中山胰腺癌 20191107.xlsx\")\n",
    "raw_df = raw_df[ ( (pd.notna(raw_df[\"华大编号\"])) & (pd.notnull(raw_df[\"华大编号\"])) )] \n",
    "data_df = {\n",
    "    \"华大编号\": list(raw_df[\"华大编号\"]), \n",
    "    \"甲基化文库名\": [ \"\" if pd.isna(raw_df[\"甲基化文库\"][idx]) or pd.isnull(raw_df[\"甲基化文库\"][idx]) else raw_df[\"甲基化文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ],\n",
    "    \"Oseq-S文库\": [ \"\" if pd.isna(raw_df[\"Oseq-S文库\"][idx]) or pd.isnull(raw_df[\"Oseq-S文库\"][idx]) else raw_df[\"Oseq-S文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ], \n",
    "    \"WGS文库\": [ \"\" if pd.isna(raw_df[\"WGS文库\"][idx]) or pd.isnull(raw_df[\"WGS文库\"][idx]) else raw_df[\"WGS文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ],\n",
    "    \"白细胞突变文库\": [ \"\" if pd.isna(raw_df[\"白细胞突变文库\"][idx]) or pd.isnull(raw_df[\"白细胞突变文库\"][idx]) else raw_df[\"白细胞突变文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ]\n",
    "}\n",
    "clinical_df, extract_df, usage_df, library_df = data_process3(data_df)\n",
    "with pd.ExcelWriter(\"test3.info.xlsx\") as writer:\n",
    "    pd.DataFrame(clinical_df).to_excel(writer, index=False, sheet_name='clinical')\n",
    "    pd.DataFrame(extract_df).to_excel(writer, index=False, sheet_name='extract')\n",
    "    pd.DataFrame(usage_df).to_excel(writer, index=False, sheet_name='usage')\n",
    "    pd.DataFrame(library_df).to_excel(writer, index=False, sheet_name='library')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df = pd.read_excel(\"中山肝癌MRD-20191113.xlsx\")\n",
    "raw_df = raw_df[ ( (pd.notna(raw_df[\"华大编号\"])) & (pd.notnull(raw_df[\"华大编号\"])) )] \n",
    "data_df = {\n",
    "    \"华大编号\": list(raw_df[\"华大编号\"]), \n",
    "    \"甲基化文库名\": [ x+\"M\" for x in raw_df[\"华大编号\"] ],\n",
    "    \"Oseq-S文库\": [ \"\" if pd.isna(raw_df[\"Oseq-S文库\"][idx]) or pd.isnull(raw_df[\"Oseq-S文库\"][idx]) else raw_df[\"Oseq-S文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ], \n",
    "    \"白细胞突变文库\": [ \"\"  for idx in range(len(raw_df[\"华大编号\"])) ]\n",
    "    # \"白细胞突变文库\": [ \"\" if pd.isna(raw_df[\"白细胞文库\"][idx]) or pd.isnull(raw_df[\"白细胞文库\"][idx]) else raw_df[\"白细胞文库\"][idx] for idx in range(len(raw_df[\"华大编号\"])) ]\n",
    "}\n",
    "clinical_df, extract_df, usage_df, library_df = data_process(data_df)\n",
    "with pd.ExcelWriter(\"test.info.xlsx\") as writer:\n",
    "    pd.DataFrame(clinical_df).to_excel(writer, index=False, sheet_name='clinical')\n",
    "    pd.DataFrame(extract_df).to_excel(writer, index=False, sheet_name='extract')\n",
    "    pd.DataFrame(usage_df).to_excel(writer, index=False, sheet_name='usage')\n",
    "    pd.DataFrame(library_df).to_excel(writer, index=False, sheet_name='library')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx in range(raw_df.shape[0]):\n",
    "    k0 = \"无\" if pd.isna(raw_df[\"肝硬化\"][idx]) or pd.isnull(raw_df[\"肝硬化\"][idx]) or raw_df[\"肝硬化\"][idx]==\"#N/A\"  else raw_df[\"肝硬化\"][idx]\n",
    "    k1 = \"无\" if pd.isna(raw_df[\"肝硬化结节\"][idx]) or pd.isnull(raw_df[\"肝硬化结节\"][idx]) or raw_df[\"肝硬化结节\"][idx]==\"#N/A\"  else raw_df[\"肝硬化结节\"][idx]\n",
    "    k2 = \"无\" if pd.isna(raw_df[\"甲胎蛋白\"][idx]) or pd.isnull(raw_df[\"甲胎蛋白\"][idx]) or raw_df[\"甲胎蛋白\"][idx]==\"#N/A\"  else raw_df[\"甲胎蛋白\"][idx]\n",
    "    k3 = \"无\" if pd.isna(raw_df[\"乙肝病毒表面抗原\"][idx]) or pd.isnull(raw_df[\"乙肝病毒表面抗原\"][idx]) or raw_df[\"乙肝病毒表面抗原\"][idx]==\"#N/A\"  else raw_df[\"乙肝病毒表面抗原\"][idx]\n",
    "    k4 = \"无\" if pd.isna(raw_df[\"乙肝病毒表面抗体\"][idx]) or pd.isnull(raw_df[\"乙肝病毒表面抗体\"][idx]) or raw_df[\"乙肝病毒表面抗体\"][idx]==\"#N/A\"  else raw_df[\"乙肝病毒表面抗体\"][idx]\n",
    "    k5 = \"无\" if pd.isna(raw_df[\"乙肝病毒e抗原\"][idx]) or pd.isnull(raw_df[\"乙肝病毒e抗原\"][idx]) or raw_df[\"乙肝病毒e抗原\"][idx]==\"#N/A\"  else raw_df[\"乙肝病毒e抗原\"][idx]\n",
    "    k6 = \"无\" if pd.isna(raw_df[\"乙肝病毒e抗体\"][idx]) or pd.isnull(raw_df[\"乙肝病毒e抗体\"][idx]) or raw_df[\"乙肝病毒e抗体\"][idx]==\"#N/A\"  else raw_df[\"乙肝病毒e抗体\"][idx]\n",
    "    k7 = \"无\" if pd.isna(raw_df[\"乙肝病毒核心抗体\"][idx]) or pd.isnull(raw_df[\"乙肝病毒核心抗体\"][idx]) or raw_df[\"乙肝病毒核心抗体\"][idx]==\"#N/A\"  else raw_df[\"乙肝病毒核心抗体\"][idx]\n",
    "    k8  = \"无\" if pd.isna(raw_df[\"乙肝病毒DNA\"][idx]) or pd.isnull(raw_df[\"乙肝病毒DNA\"][idx]) or raw_df[\"乙肝病毒DNA\"][idx]==\"#N/A\"  else raw_df[\"乙肝病毒DNA\"][idx]\n",
    "    print(\"肝硬化: {}; 肝硬化结节: {}; 甲胎蛋白: {}; 乙肝病毒表面抗原: {}; 乙肝病毒表面抗体: {}; 乙肝病毒e抗原: {}; 乙肝病毒e抗体: {}; 乙肝病毒核心抗体: {}; 乙肝病毒DNA: {}\".format(k0, k1, k2, k3, k4, k5, k6, k7, k8))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "clinicalDf = pd.read_excel(\"20200414.xlsx\", sheet_name=0)\n",
    "libraryDf = pd.read_excel(\"20200414.xlsx\", sheet_name=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "clinicalDf.head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "libraryDf.head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleDict = {}\n",
    "for idx in range(clinicalDf.shape[0]):\n",
    "    m = re.match(r'(\\d+).(\\d+)', str(clinicalDf[\"样本编号\"][idx]))\n",
    "    k = \"{}_{}\".format(m.group(1), m.group(2))\n",
    "    if k in sampleDict:\n",
    "        sampleDict[k][\"clinicalDf\"].append(idx)\n",
    "    else:\n",
    "        sampleDict[k] = {\n",
    "            \"clinicalDf\": [idx] \n",
    "            }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "repeatDict = {}\n",
    "for k in sampleDict:\n",
    "    v = len(sampleDict[k][\"clinicalDf\"])\n",
    "    if v in repeatDict:\n",
    "        repeatDict[v] +=1\n",
    "    else:\n",
    "        repeatDict[v] =1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "repeatDict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library_filt_list = []\n",
    "for idx in range(libraryDf.shape[0]):\n",
    "    s = str(libraryDf[\"华大样本编号\"][idx]).strip()\n",
    "    m = re.match(r'(\\d+).(\\d+)', s)\n",
    "    try:\n",
    "        k = \"{}_{}\".format(m.group(1), m.group(2))\n",
    "    except:\n",
    "        print(\"s: {}, idx: {}\".format(s, idx))\n",
    "        continue\n",
    "    if k in sampleDict:\n",
    "        library_filt_list.append(list(libraryDf.loc[idx]))            \n",
    "        if \"libraryDf\" in sampleDict[k]:\n",
    "            sampleDict[k][\"libraryDf\"].append(idx)\n",
    "        else:\n",
    "            sampleDict[k][\"libraryDf\"] = [idx]\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list(libraryDf.loc[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python",
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "version": "3.6.7-final"
  },
  "orig_nbformat": 2,
  "file_extension": ".py",
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3,
  "kernelspec": {
   "name": "python36764bit590e0209625c41dab6b137cee9f052d1",
   "display_name": "Python 3.6.7 64-bit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}